import logging
import sqlite3

from .iopin import IO_Pin

MACRO_DATABASE_PATH = ":memory:"  # 在内存中创建数据库（不会写入磁盘）
# MACRO_DATABASE_PATH = "test.db"  # 测试数据库

def iopin2tuple(str_iopin):  # 是不是不应该放在这里??
    iopin = IO_Pin(str_iopin)
    tuple_iopin = (
        str_iopin,
        iopin.maintype,
        iopin.subtype,
        iopin.untype
    )
    return tuple_iopin


class IOCheck_Database:
    key_suffix = "_output"
    value_suffix = "_input"
    view_suffix = "_view"

    def __init__(self):
        def db_connect():
            self.conn = sqlite3.connect(MACRO_DATABASE_PATH)  # 若不存在，则创建新数据库
            self.cursor = self.conn.cursor()  # database cursor

        db_connect()

    def __del__(self):
        logging.debug("IOCheck_Database 析构")
        self.cursor.close()
        self.conn

    def db_create_table(self, table_name):
        def db_current_table(table_name):
            # 检查table-name是否存在... but how??
            self.curr_table = table_name

        self.tb_maps = ("key_addr", "value_addr", "intro", "nonuse")
        self.cursor.execute("""CREATE TABLE {0} (
            {1} CHAR(20) NOT NULL
            , {2} CHAR(20) NOT NULL
            , {3} NVARCHAR(255)
            , {4} BOOLEAN DEFAULT FALSE
            , CHECK ({4} IN (0,1))
        ); """.format(table_name, *self.tb_maps))
        db_current_table(table_name)

        # 针对key_addr创建IO_Pin结构的分解表
        self.tb_keys = ("io_addr", "main_prefix", "sub_prefix", "untype")
        self.cursor.execute("""CREATE TABLE {} (
            {} CHAR(20) PRIMARY KEY NOT NULL
            , {} CHAR(20) NOT NULL
            , {} CHAR(20) NOT NULL
            , {} BOOLEAN
        ); """.format(table_name + self.key_suffix, *self.tb_keys))

        self.tb_values = self.tb_keys
        self.cursor.execute("""CREATE TABLE {} (
            {} CHAR(20) PRIMARY KEY NOT NULL
            , {} CHAR(20) NOT NULL
            , {} CHAR(20) NOT NULL
            , {} BOOLEAN
        ); """.format(table_name + self.value_suffix, *self.tb_values))

        self.cursor.execute("""
        CREATE VIEW {view_name} AS
        SELECT MAP.*, KEY.{main_prefix} key_{main_prefix}, KEY.{sub_prefix} key_{sub_prefix}, KEY.{untype} key_{untype},
            VALUE.{main_prefix} value_{main_prefix}, VALUE.{sub_prefix} value_{sub_prefix}, VALUE.{untype} value_{untype}
        FROM {table_map} MAP, {table_key} KEY, {table_value} VALUE
        WHERE MAP.{map_addr}=KEY.{io_addr} AND MAP.value_addr=VALUE.{io_addr};
        """.format(view_name=table_name + self.view_suffix
                , main_prefix=self.tb_keys[1], sub_prefix=self.tb_keys[2]
                , untype=self.tb_keys[3], table_map=table_name
                , table_key=table_name + self.key_suffix
                , table_value=table_name + self.value_suffix
                , map_addr=self.tb_maps[0], io_addr=self.tb_keys[0]))

    def db_drop_table(self, table_name):
        self.cursor.execute("DROP TABLE {}; ".format(table_name))
        self.cursor.execute("DROP TABLE {}; ".format(table_name + self.key_suffix))
        self.cursor.execute("DROP TABLE {}; ".format(table_name + self.value_suffix))
        self.cursor.execute("DROP VIEW {}; ".format(table_name + self.view_suffix))

    def maps_import(self, multi_maps: list, tbname_suffix=""):
        """ 一次性向数据库导入多条数据信息 """
        list_ = ["?"] * len(multi_maps[0])
        SQL = "INSERT INTO {} VALUES ({}); ".format(self.curr_table + tbname_suffix, ",".join(list_))
        logging.debug("IOCheck_Database::maps_import -->> SQL:\n\t{}".format(SQL))
        try:
            self.cursor.executemany(SQL, multi_maps)
        except sqlite3.IntegrityError:
            for map_item in multi_maps:
                try:
                    self.cursor.execute(SQL, map_item)
                except sqlite3.IntegrityError:
                    logging.debug("IOCheck_Database::maps_import() -->> 跳过重复io_addr【{}】".format(map_item[0]))
        self.conn.commit()

    def maps_export_all(self):  # replace by maps_export()
        """ return a LIST of tuple_mapping """
        list_maps = []
        for tuple_row in self.cursor.execute("SELECT * \
                FROM {} ORDER BY {}; ".format(self.curr_table, self.tb_maps[0])):
            list_maps.append(tuple_row)
        return list_maps

    def maps_export(self, **kwargs):  # 根据表格设计，一般过滤查询都需要连接多个数据库
        """ return a LIST of tuple_mapping after filter by 'where' sentence.
            kwargs:
                "view"      : VIEW only
                - "column"  : list of select fields, 若为空，则选择tb_maps表中的全部字段
                - "where"   : string of condition,   若为空，则不过滤数据
        """
        sel_columns = ",".join(kwargs["column"]) if "column" in kwargs else ",".join(self.tb_maps)
        str_where = "WHERE " + kwargs["where"] if "where" in kwargs and kwargs["where"] else ""
        order_by = kwargs["order"] if "order" in kwargs else self.tb_maps[0]

        SQL ="SELECT {column} FROM {view} {condition} ORDER BY {order}; ".format(
            column=sel_columns, view=self.curr_table+self.view_suffix,
            condition=str_where, order=order_by)
        logging.debug("IOCheck_Database::maps_export -->> SQL:\n\t{}".format(SQL))

        list_maps = []
        for tuple_row in self.cursor.execute(SQL):
            list_maps.append(tuple_row)
        return list_maps

    def maps_update(self, column_index, key, new_value, previous_value):
        if column_index == 3:
            new_value = 1 if new_value == "True" else 0

        SQL = "UPDATE {} SET {}='{}' WHERE {}='{}'; ".format(self.curr_table, self.tb_maps[column_index],
                                                           new_value, self.tb_maps[0], key)
        logging.debug("IOCheck_Database::maps_update -->> SQL:\n\t{}".format(SQL))
        self.cursor.execute(SQL)

        def maps_update_related_table(table_name_suffix):
            table_name = self.curr_table + table_name_suffix
            # 删除原数据
            SQL = "DELETE FROM {} WHERE {}='{}'; ".format(table_name, self.tb_keys[0], previous_value)
            logging.debug("IOCheck_Database::maps_update -->> SQL:\n\t{}".format(SQL))
            self.cursor.execute(SQL)
            # 导入新数据
            self.maps_import([iopin2tuple(new_value)], table_name_suffix)

        # 如修改了io_addr，则需要同时改写key表和value表
        if column_index == 0:  # field: key_addr, value_addr
            maps_update_related_table(self.key_suffix)
        elif column_index == 1:
            maps_update_related_table(self.value_suffix)

        self.conn.commit()

    def maps_insert(self, tb_name, tuple_item):
        """ 数据表中插入一条数据 """
        SQL = "INSERT INTO {} VALUES ({}); ".format(tb_name, ",".join(["?"] * len(tuple_item)))
        logging.debug("IOCheck_Database::maps_update -->> SQL:\n\t{}".format(SQL))
        self.cursor.execute(SQL, tuple_item)

        self.conn.commit()

    def maps_delete(self, tuple_item):
        list_ = []
        for index in range(len(tuple_item)):
            list_.append("{}='{}'".format(self.tb_maps[index], tuple_item[index]))
        where = " AND ".join(list_)
        SQL = "DELETE FROM {} WHERE {}; ".format(self.curr_table, where)
        logging.debug("IOCheck_Database::maps_delete -->> SQL:\n\t{}".format(SQL))
        self.cursor.execute(SQL)
        # 并不更改 key_addr/value_addr 数据表 ??

        self.conn.commit()

    def maps_getitem(self, key, column_field):
        pass